
# pip install sqlalchemy psycopg2
# pip install sqlalchemy psycopg


# https://blog.csdn.net/wlddhj/article/details/138675741

# https://docs.sqlalchemy.org/en/20/dialects/postgresql.html

from sqlalchemy import create_engine

# 替换为你的MySQL数据库信息
username = 'postgres'
password = 'qw123666'
host = '192.168.0.146'  # 例如：'localhost' 或 '127.0.0.1'
port = 5433  # 通常是 3306
database = 'postgres'

"postgresql+psycopg://user:password@host:port/dbname[?key=value&key=value...]"
uri = "postgresql+psycopg://{username}:{password}@{host}:{port}/{dbname}".format(
    username=username,
    password=password,
    host=host,
    port=port,
    dbname=database,
)
# 创建连接引擎
engine = create_engine(uri)


from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)


Base.metadata.create_all(engine)

####################################
# 添加数据
####################################
from sqlalchemy.orm import sessionmaker

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

# 添加新用户
new_user = User(name='John Doe', email='john.doe@example.com')
session.add(new_user)
session.commit()

# 关闭会话
session.close()


##########################
# 查询数据
session = Session()

# 查询所有用户
users = session.query(User).all()
for user in users:
    print(f"User ID: {user.id}, Name: {user.name}, Email: {user.email}")

# 关闭会话
session.close()